package com.blacktech.dbu.meta.service;

import com.blacktech.dbu.core.dict.DatabaseDialect;
import com.blacktech.dbu.core.exception.BusinessException;
import com.blacktech.dbu.core.response.ResponseCode;
import com.blacktech.dbu.meta.entity.DbuSchema;
import com.blacktech.dbu.meta.repository.ProductRepository;
import com.blacktech.dbu.meta.repository.SchemaRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

/**
 * 数据库模式管理服务
 *
 * @author Yanyunsong
 */
@Service
@Transactional
public class SchemaService {

    private final SchemaRepository schemaRepository;
    private final ProductRepository productRepository;

    @Autowired
    public SchemaService(SchemaRepository schemaRepository, ProductRepository productRepository) {
        this.schemaRepository = schemaRepository;
        this.productRepository = productRepository;
    }

    /**
     * 创建数据库模式
     */
    public DbuSchema createSchema(DbuSchema schema) {
        // 验证产品是否存在
        if (schema.getProduct() == null || schema.getProduct().getId() == null) {
            throw new BusinessException(ResponseCode.BAD_REQUEST.getCode(), "产品ID不能为空");
        }

        if (!productRepository.existsById(schema.getProduct().getId())) {
            throw new BusinessException(ResponseCode.NOT_FOUND.getCode(), "产品不存在");
        }

        // 验证Schema名称是否已存在
        if (schemaRepository.existsByProductIdAndSchemaName(schema.getProduct().getId(), schema.getSchemaName())) {
            throw new BusinessException(ResponseCode.BUSINESS_ERROR.getCode(),
                "Schema名称 '" + schema.getSchemaName() + "' 在该产品下已存在");
        }

        // 验证必填字段
        validateSchema(schema);

        return schemaRepository.save(schema);
    }

    /**
     * 更新数据库模式
     */
    public DbuSchema updateSchema(Long id, DbuSchema schema) {
        Optional<DbuSchema> existingSchemaOpt = schemaRepository.findById(id);
        if (existingSchemaOpt.isEmpty()) {
            return null;
        }

        DbuSchema existingSchema = existingSchemaOpt.get();

        // 如果产品发生变化，验证新产品是否存在
        if (schema.getProduct() != null && schema.getProduct().getId() != null &&
            !existingSchema.getProduct().getId().equals(schema.getProduct().getId())) {

            if (!productRepository.existsById(schema.getProduct().getId())) {
                throw new BusinessException(ResponseCode.NOT_FOUND.getCode(), "产品不存在");
            }

            // 检查新产品下Schema名称是否已存在
            if (schemaRepository.existsByProductIdAndSchemaName(schema.getProduct().getId(), schema.getSchemaName())) {
                throw new BusinessException(ResponseCode.BUSINESS_ERROR.getCode(),
                    "Schema名称 '" + schema.getSchemaName() + "' 在目标产品下已存在");
            }
        } else if (schema.getProduct() == null) {
            // 如果请求中没有产品信息，保持原有产品
            schema.setProduct(existingSchema.getProduct());
        }

        // 如果Schema名称发生变化，检查新名称是否已存在
        if (!existingSchema.getSchemaName().equals(schema.getSchemaName())) {
            Long productId = schema.getProduct() != null ? schema.getProduct().getId() : existingSchema.getProduct().getId();
            if (schemaRepository.existsByProductIdAndSchemaNameAndIdNot(productId, schema.getSchemaName(), id)) {
                throw new BusinessException(ResponseCode.BUSINESS_ERROR.getCode(),
                    "Schema名称 '" + schema.getSchemaName() + "' 已存在");
            }
        }

        // 更新字段
        updateSchemaFields(existingSchema, schema);

        return schemaRepository.save(existingSchema);
    }

    /**
     * 批量删除数据库模式
     */
    @Transactional
    public int batchDeleteSchemas(List<Long> ids) {
        if (ids == null || ids.isEmpty()) {
            return 0;
        }

        // 检查是否存在关联的数据，如tables等
        for (Long id : ids) {
            if (!schemaRepository.existsById(id)) {
                throw new BusinessException(ResponseCode.NOT_FOUND.getCode(),
                    "Schema ID " + id + " 不存在");
            }
        }

        // 执行删除
        schemaRepository.deleteAllById(ids);
        return ids.size();
    }

    /**
     * 验证Schema信息
     */
    private void validateSchema(DbuSchema schema) {
        if (schema.getSchemaName() == null || schema.getSchemaName().trim().isEmpty()) {
            throw new BusinessException(ResponseCode.BAD_REQUEST.getCode(), "Schema名称不能为空");
        }

        if (schema.getDbType() == null) {
            throw new BusinessException(ResponseCode.BAD_REQUEST.getCode(), "数据库类型不能为空");
        }

        // Schema名称长度限制
        if (schema.getSchemaName().length() > 128) {
            throw new BusinessException(ResponseCode.BAD_REQUEST.getCode(), "Schema名称长度不能超过128个字符");
        }

        // 描述长度限制
        if (schema.getDescription() != null && schema.getDescription().length() > 500) {
            throw new BusinessException(ResponseCode.BAD_REQUEST.getCode(), "描述长度不能超过500个字符");
        }

        // Schema名称格式验证（字母数字下划线，以字母开头）
        if (!schema.getSchemaName().matches("^[a-zA-Z][a-zA-Z0-9_]*$")) {
            throw new BusinessException(ResponseCode.BAD_REQUEST.getCode(),
                "Schema名称只能包含字母、数字和下划线，且必须以字母开头");
        }
    }

    /**
     * 更新Schema字段
     */
    private void updateSchemaFields(DbuSchema existingSchema, DbuSchema updateSchema) {
        if (updateSchema.getProduct() != null && updateSchema.getProduct().getId() != null) {
            existingSchema.setProduct(updateSchema.getProduct());
        }

        if (updateSchema.getSchemaName() != null) {
            existingSchema.setSchemaName(updateSchema.getSchemaName());
        }

        if (updateSchema.getDbType() != null) {
            existingSchema.setDbType(updateSchema.getDbType());
        }

        if (updateSchema.getDescription() != null) {
            existingSchema.setDescription(updateSchema.getDescription());
        }

        // 注意：这里不更新ID和创建时间等系统字段
    }

    /**
     * 根据产品ID和Schema名称查找Schema
     */
    @Transactional(readOnly = true)
    public DbuSchema findByProductIdAndSchemaName(Long productId, String schemaName) {
        return schemaRepository.findByProductIdAndSchemaName(productId, schemaName);
    }

    /**
     * 检查Schema是否存在
     */
    @Transactional(readOnly = true)
    public boolean existsByProductIdAndSchemaName(Long productId, String schemaName) {
        return schemaRepository.existsByProductIdAndSchemaName(productId, schemaName);
    }

    /**
     * 根据产品ID获取Schema列表
     */
    @Transactional(readOnly = true)
    public List<DbuSchema> getSchemasByProductId(Long productId) {
        return schemaRepository.findByProductIdOrderBySchemaName(productId);
    }

    /**
     * 根据数据库类型获取Schema列表
     */
    @Transactional(readOnly = true)
    public List<DbuSchema> getSchemasByDatabaseType(DatabaseDialect databaseType) {
        return schemaRepository.findByDbType(databaseType);
    }
}